<?php

/**
 * Created by muniao.
 * User: muniao
 * Date: 2018/5/31
 * Time: 15:20
 */

namespace app\api\controller\android\v13;


use shortLink\WxService;
use think\Controller;
use think\Request;
use app\api\model\OrderModel;
use think\Db;
use app\api\model\Authority;
use redis\RedisPackage;
use think\Exception;

class WorkBench extends Controller
{
    protected $us;

    public function _initialize()
    {
        $this->us = Authority::check(1);
    }

    public function index()
    {
        $thirtyDaysAgo = time() - 30 * 86400;
        //本月
        $BeginDate = date('Y-m-01', strtotime(date("Y-m-d", time())));
        $starttime = strtotime($BeginDate);
        $enttime   = strtotime(date('Y-m-d', strtotime("$BeginDate +1 month -1 day")));
        //本日
        $beginToday = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
        $endToday   = mktime(0, 0, 0, date('m'), date('d') + 1, date('Y')) - 1;
        //带上门
        $list['door'] = db('workbench_read')->where('user_id', $this->us['user_id'])->where('already', 1)->whereBetween('time', [$beginToday, $endToday])->count();
        //已关注
        $list['point'] = db('order')->where('point', 2)->where('assignor', $this->us['user_id'])->count();
        $list['orderInventoryRecord']=db('order_inventory_record', config('database.zong'))
            ->join('order', 'order.order_id = order_inventory_record.order_id', 'left')
            ->where('order.assignor',$this->us['user_id'])
             ->where('order_inventory_record.read_time',0)
            ->count();
        //待邀约到店
        $list['clockIntore'] = db('envelopes')
            ->alias('e')
            ->join('clock_in_store cis', 'cis.order_id = e.ordesr_id', 'left')
            ->join('order', 'order.order_id = e.ordesr_id', 'left')
            ->join('order_times', 'order.order_id=order_times.order_id', 'left')
            ->whereNull('cis.id')
            ->where('order_times.signing_time', 0)
            ->whereBetween('order.state', [1, 4])
            ->where('order.assignor', $this->us['user_id'])
            ->where('e.total_price', '>=', 30000)
            ->where('e.created_time', '>=', $thirtyDaysAgo)
            ->group('e.ordesr_id')->count();

        //待创建施工群订单列表
         $list['constructionGroupToBeBuilt'] = db('order',config('database.zong'))
            ->join('order_info', 'order.order_id = order_info.order_id', 'left')
            ->join('envelopes', 'envelopes.ordesr_id = order_info.order_id and envelopes.type=1', 'left')
            ->join('order_times', 'order.order_id=order_times.order_id', 'left')
            ->join('order_unique_code', 'order.order_id=order_unique_code.order_id', 'left')
            ->whereBetween('order.state', [4, 7])
            ->where('if(order.order_agency=1,order.cleared_time IS NULL and order.settlement_time  IS NULL,order.cleared_time  IS NULL )')
            ->where('UNIX_TIMESTAMP(FROM_UNIXTIME(order_times.change_work_time,"%Y-%m-%d 00:00:00")) > unix_timestamp(now()) - 4*24*3600')
            ->where('order_unique_code.is_crowd', 0)
            ->where('order.created_time', '>', 1751596804)
            ->where('order.assignor', $this->us['user_id'])
            ->where('order_info.gong', '>', 1)->count();
        //待处理
        $list['communicate'] = db('through')->Join('order', 'order.order_id=through.order_ids', 'left')->where('role', 2)->whereBetween('end_time', [$beginToday, $endToday])->where('through.admin_id', $this->us['user_id'])->whereNull('through.handle')->group('through.order_ids')->count();
        $userId=$this->us['user_id'];
         $list['newHandover'] = db('order')
         ->field([
            'order.order_id',
            'order.cleared_time',
            'order.settlement_time',
            'ANY_VALUE(order.addres) as address', // 非聚合字段处理‌:ml-citation{ref="6" data="citationList"}
            // 'startup.sta_time',
            'FROM_UNIXTIME(order_times.signing_time,"%Y-%m-%d %H:%i:%s")as signing_time',
            // 'MAX(order_times.change_work_time) as change_work_time',
            'SUM(IF(startup_new_handover.type=1,1,0)) as type1_total',
            'SUM(IF(startup_new_handover.type=2,1,0)) as type2_total'
        ])
            ->join('order_aggregate', 'order.order_id=order_aggregate.order_id', 'left')
            ->join('startup_new_handover', 'order.order_id=startup_new_handover.order_id AND startup_new_handover.delete_time=0', 'LEFT')
            ->join('order_times', 'order_times.order_id=order.order_id', 'LEFT')
            ->where(function ($query) use ($userId) {
                $query->where('order.assignor', $userId);
                    // ->whereOr('order.deliverer', $userId);
            })->where('CASE
            WHEN order_aggregate.main_price=0 and order_aggregate.agent_price !=0  THEN order.settlement_time is  null or order.settlement_time =0
             WHEN order_aggregate.main_price !=0 and order_aggregate.agent_price =0  THEN  order.cleared_time is  null or order.cleared_time =0
             WHEN order_aggregate.main_price !=0 and order_aggregate.agent_price !=0 THEN  order.cleared_time is  null or order.cleared_time =0 or order.settlement_time is null or order.settlement_time =0 END
             ')
            ->where('order_times.change_work_time', '>', 1743134400)
            ->whereNull('order.cleared_time')
           ->group('startup_new_handover.order_id')
            ->having('type1_total = 0 OR type2_total = 0 OR startup_new_handover.order_id IS NULL')
            ->count();
        //审批提醒
        $list['approvalReminder'] = db('approval_info', config('database.zong'))->where('user_id', $this->us['user_id'])->where('user_type', 1)->where('read_time', 0)->where('approval_status', 2)->count();

        //应收款总计
        $list['receivable'] = db('order', config('database.zong'))->Join('order_aggregate', 'order_aggregate.order_id=order.order_id', 'left')->where('order.assignor', $this->us['user_id'])->whereBetween('order.state', [4, 7])->sum('total_price-payment_price');

        //本月因收款
        $list['monthReceivable'] = db('order', config('database.zong'))->Join('order_aggregate', 'order_aggregate.order_id=order.order_id', 'left')->where('order.assignor', $this->us['user_id'])->whereBetween('finish_time', [$starttime, $enttime])->sum('total_price-payment_price');

        //超期因收款
        $list['overdueReceivable'] = db('order', config('database.zong'))->Join('order_aggregate', 'order_aggregate.order_id=order.order_id', 'left')->join('order_info', 'order_info.order_id=order.order_id', 'left')->where('order.assignor', $this->us['user_id'])->where('order.state', 7)->where('total_price-payment_price>0')->where('(UNIX_TIMESTAMP(NOW())-`finish_time`) >3600*24*3')->sum('total_price-payment_price');


        //待开工
        $list['started'] = db('order', config('database.zong'))->where(function ($quer) {
            $quer->whereNull('order.start_time')->whereOr('order.start_time', 0);
        })->where('order.state', 4)->where('order.assignor', $this->us['user_id'])->count('order_id');
        //施工中
        $list['commencement'] = db('order', config('database.zong'))->where('order.assignor', $this->us['user_id'])->where(function ($quer) {
            $quer->whereNotNull('order.start_time')->where('order.start_time', '<>', 0);
        })->where('order.state', 4)->count('order_id');
        //完工待结算
        $list['beSettled'] = db('order', config('database.zong'))->where('order.assignor', $this->us['user_id'])->where('order.state', 7)->where('if(order.order_agency=1,order.cleared_time IS NULL and order.settlement_time  IS NULL,order.cleared_time  IS NULL )')->count('order_id');

        //本月已结算
        $list['settled'] = db('order', config('database.zong'))->where('order.assignor', $this->us['user_id'])->where('if(order.order_agency=1,order.cleared_time between ' . $starttime . ' and ' . $enttime . ' and order.settlement_time between ' . $starttime . ' and ' . $enttime . ',order.cleared_time between ' . $starttime . ' and ' . $enttime . ')')
            //            ->where(function ($quer) use ($starttime, $enttime) {
            //                $quer->whereBetween('cleared_time', [$starttime, $enttime])->whereOr('settlement_time', 'between', [$starttime, $enttime]);
            //            })
            ->count('order_id');
        $materialDisplay = 0; //隐藏
        if (in_array($this->us['user_id'], ['241000241000304', '241000000000036']) || $this->us['mobile'] == '13980986296') {
            $materialDisplay = 1; //显示
        }
        $list['materialDisplay'] = $materialDisplay;
        //待下单主材
        $list['pendingOrderPlacement'] = \db('order')
            ->join('agency_task', 'agency_task.order_id=order.order_id')
            ->where('agency_task.agency_id', 0)->where('agency_task.agency_staff_id', 0)->where('order.assignor', $this->us['user_id'])->where('agency_task.delete_time', 0)->where('DATEDIFF(NOW(), FROM_UNIXTIME(order.start_time,"%Y-%m-%d"))>=7')
            ->where(function ($quer) {
                $quer->whereNull('order.settlement_time')->whereOr('order.settlement_time', 0);
            })
            ->group('order.order_id')->count();
        //待确认方案
        $list['planToBeConfirmed'] = \db('order')->join('agency_order', 'agency_order.order_id=order.order_id')->where('order.assignor', $this->us['user_id'])->where('agency_order.status', 2)->where(function ($quer) {
            $quer->whereNull('order.settlement_time')->whereOr('order.settlement_time', 0);
        })->where('agency_order.delete_time', 0)->group('order.order_id')->count();
        //用户确认中
        $list['userConfirmationInProgress'] = \db('order')->join('agency_order', 'agency_order.order_id=order.order_id')->where('order.assignor', $this->us['user_id'])->where('agency_order.status', 3)->where('agency_order.delete_time', 0)->where(function ($quer) {
            $quer->whereNull('order.settlement_time')->whereOr('order.settlement_time', 0);
        })->group('order.order_id')->count();
        //验收主材
        $list['acceptanceOfMainMaterials'] = \db('order')->join('agency_order', 'agency_order.order_id=order.order_id', 'left')->join('agency_task', 'agency_task.agency_order_id=agency_order.id', 'left')->join('capital', 'capital.capital_id=agency_task.capital_id', 'left')->where('agency_order.status', 13)->where('capital.acceptance', 0)->where('capital.types', 1)->where('capital.enable', 1)->where('capital.acceptance', 0)->field('order.addres,order.order_id,if(agency_order.deliverable_time=0,"",FROM_UNIXTIME(agency_order.deliverable_time,"%Y-%m-%d "))  as createdTime,concat(capital.class_b) as title')->where('agency_task.delete_time', 0)->where('order.assignor', $this->us['user_id'])->where(function ($quer) {
            $quer->whereNull('order.settlement_time')->whereOr('order.settlement_time', 0);
        })->order('createdTime desc')->group('order.order_id')->count();
        //待验收节点
        $auxiliary_delivery_node = \db('auxiliary_delivery_node')->field('auxiliary_delivery_schedul_id,FROM_UNIXTIME(auxiliary_delivery_node.upload_time,"%Y-%m-%d") as createdTime')->where('auxiliary_delivery_node.state', 0)->buildSql();

        $auxiliary_delivery_schedule = \db('auxiliary_delivery_schedule')->field('auxiliary_delivery_schedule.auxiliary_project_list_id,node.*')->join([$auxiliary_delivery_node => 'node'], 'node.auxiliary_delivery_schedul_id=auxiliary_delivery_schedule.id', 'right')->whereNull('auxiliary_delivery_schedule.delete_time')->group('auxiliary_project_list_id')->buildSql();


        $acceptance         = db('order')->field('schedule.*,order.addres,order.order_id,concat(capital.class_b) as title')->join('auxiliary_project_list', 'order.order_id=auxiliary_project_list.order_id', 'left')->join([$auxiliary_delivery_schedule => 'schedule'], 'auxiliary_project_list.id=schedule.auxiliary_project_list_id', 'right')->join('capital', 'capital.capital_id=auxiliary_project_list.capital_id', 'left')->where('order.state', '>', 3)->whereNull('auxiliary_project_list.completion_time')->where('capital.types', 1)->where('capital.enable', 1)->whereNull('auxiliary_project_list.delete_time')->where('order.assignor', $this->us['user_id'])->order('createdTime desc')->group('order.order_id')->count();
        $list['acceptance'] = $acceptance;
        //        //完工带验收
        //        $work_checkImg = db('order')->join('work_check', 'order.order_id=work_check.order_id', 'left')->where('order.assignor', $this->us['user_id'])->where('work_check.work_title_id', 5)->where('delete_time', 0)->find();
        r_date($list, 200);
    }

    /*
     * 新订单列表
     */
    public function newList()
    {
        $data      = \request()->get();
        $BeginDate = date('Y-m-01', strtotime(date("Y-m-d", time())));
        $starttime = strtotime($BeginDate);
        $enttime   = strtotime(date('Y-m-d', strtotime("$BeginDate +1 month -1 day")));
        $payment   = db('payment')->field('sum(money) as money,orders_id')->where("IF(payment.weixin=2,payment.success=2,payment.success=1)")->group('orders_id')->buildSql();
        $capital   = db('capital')->field('sum(to_price) as to_price,ordesr_id')->where(['capital.types' => 1, 'capital.enable' => 1])->group('ordesr_id')->buildSql();
        $envelopes = db('envelopes')->field('sum(envelopes.give_money) as give_money,sum(envelopes.expense) as expense,sum(envelopes.purchasing_discount) as purchasing_discount,sum(envelopes.purchasing_expense) as purchasing_expense,ordesr_id,envelopes.gong')->where(['envelopes.type' => 1])->group('ordesr_id')->buildSql();
        $list      = db('order')->join([$envelopes => 'envelopes'], 'envelopes.ordesr_id=order.order_id', 'left')->join([$capital => 'capital'], 'capital.ordesr_id=order.order_id', 'left')->join([$payment => 'payment'], 'payment.orders_id=order.order_id', 'left')->join('contract', 'contract.orders_id=order.order_id', 'left')->join('province', 'order.province_id=province.province_id', 'left')->join('city', 'order.city_id=city.city_id', 'left')->join('county', 'order.county_id=county.county_id', 'left');
        switch ($data['type']) {
            case 1:
                //                $list->where('(order.created_time is null or order.created_time=0)' or ('order.settlement_time is null or order.settlement_time=0'))
                //                $list->order('contract.con_time desc');
                break;
            case 2:
                $list->whereBetween('finish_time', [$starttime, $enttime]);
                break;
            case 3:
                $list->where('(UNIX_TIMESTAMP(NOW())-`finish_time`) >3600*24*3')->where('order.state', 7)->where('ifnull((capital.to_price + (envelopes.expense-envelopes.give_money-envelopes.purchasing_discount+envelopes.purchasing_expense)),0)-ifnull(money,0)>0');
                break;
            case 4:
                $list->where(function ($quer) {
                    $quer->whereNull('order.start_time')->whereOr('order.start_time', 0);
                })->where('order.state', 4);


                break;
            case 5:
                $list->where(function ($quer) {
                    $quer->whereNotNull('order.start_time')->where('order.start_time', '<>', 0);
                })->where('order.state', 4);

                break;
            case 6:
                $list->where('order.state', 7)->where('if(order.order_agency=1,order.cleared_time IS NULL and order.settlement_time  IS NULL,order.cleared_time  IS NULL )');

                break;

            default:
                //                $list->where(function ($quer) use ($starttime, $enttime) {
                //                    $quer->whereBetween('order.cleared_time', [$starttime, $enttime])->whereOr('order.settlement_time', 'between', [$starttime, $enttime]);
                //                });
                $list->where('if(order.order_agency=1,order.cleared_time between ' . $starttime . ' and ' . $enttime . ' and order.settlement_time between ' . $starttime . ' and ' . $enttime . ',order.cleared_time between ' . $starttime . ' and ' . $enttime . ')');
        }
        if ($data['sort'] == 1) {
            $xu = "desc";
        } else {
            $xu = "asc";
        }
        switch ($data['status']) {
            case 1:
                $list->order('contract.con_time ' . $xu . '');

                break;
            case 2:
                $list->order('order.start_time ' . $xu . '');
                break;
            default:
                $list->order('order.finish_time ' . $xu . '');
        }
        $listdata = $list->where('order.assignor', $this->us['user_id'])->whereBetween('order.state', [4, 7])->field('ifnull((capital.to_price + (envelopes.expense-envelopes.give_money-envelopes.purchasing_discount+envelopes.purchasing_expense)),0) as signing,ifnull(money,0) as money,gong,FROM_UNIXTIME(contract.con_time,"%Y-%m-%d ") as con_time,if(ifnull(order.start_time,0)=0," ",FROM_UNIXTIME(order.start_time,"%Y-%m-%d ")) as start_time,if(ifnull(order.finish_time,0)=0," ",FROM_UNIXTIME(order.finish_time,"%Y-%m-%d ")) as finish_time,concat(province.province,city.city,county.county,order.addres) as  addres,order.contacts,order.telephone,order.order_id,if(ifnull(order.start_time,0)=0,0,FLOOR(if(ifnull(order.finish_time,0)=0,(UNIX_TIMESTAMP(NOW())-order.start_time),(order.finish_time-order.start_time))/(3600*24))) as nowDay')->page($data['page'], $data['limit'])->select();
        r_date($listdata, 200);
    }

    public function getList()
    {
        $data = \request()->get();
        switch ($data['type']) {
            case 1:
                $list =  db('order', config('database.zong'))->join('order_info', 'order_info.order_id=order.order_id', 'left')->join('startup', 'startup.orders_id=order.order_id', 'left')->where('CEIL((UNIX_TIMESTAMP(NOW())-startup.up_time)/(3600*24)) >0')->whereNotNull('startup.up_time')->where('startup.up_time', '<>', 0)->where('order.state', 4)->field('order.order_id,concat(order_info.county,order.addres) as  addres,CEIL((UNIX_TIMESTAMP(NOW())-startup.up_time)/(3600*24)) as  dat,concat("超",CEIL((UNIX_TIMESTAMP(NOW())-startup.up_time)/(3600*24)),"天") as overtime')->order('dat asc');
                break;
            case 2:

                $payment = db('payment')->field('sum(money) as money,orders_id')->where("IF(payment.weixin=2,payment.success=2,payment.success=1)")->group('orders_id')->buildSql();
                $list    = db('order')->join('contract', 'contract.orders_id=order.order_id', 'left')->join([$payment => 'payment'], 'payment.orders_id=order.order_id', 'left')->join('county', 'order.county_id=county.county_id', 'left')->where('money', '<>', 0)->where('order.state', 4)->where(function ($quer) {
                    $quer->whereNull('order.start_time')->whereOr('order.start_time', 0);
                })->field('order.order_id,concat(county.county,order.addres) as  addres,concat("超",FLOOR((UNIX_TIMESTAMP(NOW())-contract.con_time)/(3600*24)),"天") as  overtime,FLOOR((UNIX_TIMESTAMP(NOW())-contract.con_time)/(3600*24)) as  dat')->order('dat desc');
                break;
            case 3:
                $list = db('order', config('database.zong'))->join('order_info', 'order_info.order_id=order.order_id', 'left')->Join('order_aggregate', 'order_aggregate.order_id=order.order_id', 'left')->where('`order_aggregate`.`total_price`<>`order_aggregate`.`payment_price`')->where('state', 7)->field('order.order_id,concat(order_info.county,order.addres) as  addres,concat("待收",round((order_aggregate.total_price-order_aggregate.payment_price),2),"元") as overtime,round((order_aggregate.total_price-order_aggregate.payment_price),2) as dat')->order('dat desc');
                break;
            case 4:
                $list = db('order', config('database.zong'))->join('order_info', 'order_info.order_id=order.order_id', 'left')->join('startup', 'startup.orders_id=order.order_id', 'left')->where('abs((UNIX_TIMESTAMP(NOW())-startup.up_time)/(3600*24))< (`gong`* 0.25)')->where('(UNIX_TIMESTAMP(NOW())-startup.up_time)/(3600*24)<0')->whereNotNull('startup.up_time')->where('startup.up_time', '<>', 0)->where('state', 4)->field('order.order_id,concat(order_info.county,order.addres) as addres,CEIL((UNIX_TIMESTAMP(NOW())-startup.up_time)/(3600*24)) as  dat,concat("剩余",abs(CEIL((UNIX_TIMESTAMP(NOW())-startup.up_time)/(3600*24))),"天") as overtime')->order('dat desc');
                break;

            default:
                $list = db('order', config('database.zong'))->join('order_info', 'order_info.order_id=order.order_id', 'left')->where('order.state', 'between', [4, 7])->Join('order_aggregate', 'order_aggregate.order_id=order.order_id', 'left')->where('`order_aggregate`.`total_price` * 0.5 >`order_aggregate`.`payment_price`')->field('order.order_id,concat(order_info.county,order.addres) as  addres,concat("待收",round((order_aggregate.total_price- order_aggregate.payment_price),2),"元") as  overtime,round((order_aggregate.total_price- order_aggregate.payment_price),2) as  dat')->order('dat desc');
        }
        if (isset($data['more']) && $data['more'] == 1) {
            $getList['list'] = $list->where('order.assignor', $this->us['user_id'])->page(0, 3)->select();
        } else {
            $getList['list'] = $list->where('order.assignor', $this->us['user_id'])->select();
        }



        r_date($getList, 200);
    }

    public function Ranking($type, $mode = 1)
    {

        $BeginDate = date('Y-m-01', strtotime(date("Y-m-d", time())));
        $starttime = strtotime($BeginDate);
        $enttime   = strtotime(date('Y-m-d', strtotime("$BeginDate +1 month -1 day")));
        $userList  = \db('user')->where('user.show_ranking', 1)->where('user.status', 0)->field('username,avatar,user_id')->select();
        //        $userList = \db('user')->where('user.status', 0)->field('username,avatar,user_id')->select();
        $user = array_column($userList, 'user_id');

        switch ($type) {
            case 1:
                $list = $this->achievement($starttime, $enttime, $user);
                break;
            case 2:
                $list = $this->Conversion($starttime, $enttime, $user);
                break;
            case 3:
                $list = $this->Transfe($starttime, $enttime, $user);
                break;
            default:
                $list = $this->profit($starttime, $enttime, $user);
                break;
        }
        $userDataList = [];

        foreach ($userList as $k => $item) {
            $zeng                         = array_sum(array_column((new Financial)->zeng($starttime, $enttime, $item['user_id']), 'turnover'));
            $jian                         = array_sum(array_column((new Financial)->jian($starttime, $enttime, $item['user_id']), 'turnover'));
            $tui                          = array_sum(array_column((new Financial)->tui($starttime, $enttime, $item['user_id']), 'turnover'));
            $userDataList[$k]['username'] = $item['username'];
            $userDataList[$k]['value']    = sprintf('%.2f', $zeng - $jian - $tui);
            $userDataList[$k]['avatar']   = $item['avatar'];
            $userDataList[$k]['user_id']  = $item['user_id'];
        }

        foreach ($userDataList as $key => $valueA) {
            $id = $valueA['user_id'];
            foreach ($list as $keys => $valueB) {
                if ($id == $valueB['user_id']) {
                    unset($userDataList[$key]);
                }
            }
        }


        $list = array_merge($userDataList, $list);


        $last_data = array_column($list, 'value');
        array_multisort($last_data, SORT_DESC, $list);
        $dataList = [];


        foreach ($list as $k => $item) {
            $dataList[$k]['xu']       = $k + 1;
            $dataList[$k]['username'] = $item['username'];
            $dataList[$k]['value']    = $item['value'];
            $dataList[$k]['avatar']   = $item['avatar'];
            $dataList[$k]['user_id']  = $item['user_id'];
        }


        $count = count($dataList) * 0.1;

        $count = ceil($count);

        $lsit = [];
        for ($i = 1; $i <= $count; $i++) {
            $lsit[] = $dataList[count($dataList) - $i];
        }

        $last_datas = array_column($lsit, 'xu');
        array_multisort($last_datas, SORT_ASC, $lsit);
        if ($mode == 1) {
            r_date(['info' => "\r \r \r \r \r排名数据截止时间：" . date('Y-m-d H:i:s', time()) . " \n 业绩和转化率排名计入末位淘汰,复转率和利润暂不计算", 'list' => $dataList, 'lastList' => $lsit], 200);
        } else {
            return $dataList;
        }
    }

    /*
     * 业绩
     */
    public function achievement($starttime, $enttime, $user)
    {

        $list = [];
        foreach ($user as $k => $item) {
            $Calculate            = (new ReserveWage())->CalculatePerformance($item, $starttime, $enttime, 3);
            $zeng                 = array_sum(array_column((new Financial)->zeng($starttime, $enttime, $item), 'turnover'));
            $jian                 = array_sum(array_column((new Financial)->jian($starttime, $enttime, $item), 'turnover'));
            $tui                  = array_sum(array_column((new Financial)->tui($starttime, $enttime, $item), 'turnover'));
            $list[$k]['value']    = sprintf('%.2f', array_sum(array_column($Calculate, 'amount')) + $zeng - abs($jian) - abs($tui));
            $userFind             = db('user')->where('user_id', $item)->find();
            $list[$k]['avatar']   = $userFind['avatar'];
            $list[$k]['username'] = $userFind['username'];
            $list[$k]['user_id']  = $item;
        }
        return $list;
    }

    /*
     * 转化率排名
     */
    public function Conversion($start_timestamp, $end_timestamp, $user, $type = 1)
    {
        $_list = db('order')->join('user user', 'user.user_id = order.assignor', 'left')->join('contract contract', 'contract.orders_id = order.order_id', 'left');
        if ($end_timestamp <= 1704038399) {
            $_list->where('(order.created_time between ' . $start_timestamp . ' and ' . $end_timestamp . ') or (contract.con_time between ' . $start_timestamp . ' and ' . $end_timestamp . ') or (order.state=9 and contract.con_time<' . $start_timestamp . ' and order.tui_time between ' . $start_timestamp . ' and ' . $end_timestamp . ') ')->field('order.order_id,order.assignor,order.tui_time,user.username,order.created_time,order.state,order.channel_id,order.channel_details,contract.con_time,user.avatar,user.user_id');
        } else {
            $_list->join('order_times', 'order_times.order_id=order.order_id', 'left')->where('(order_times.dispatch_time between ' . $start_timestamp . ' and ' . $end_timestamp . ') or (order_times.change_work_time between ' . $start_timestamp . ' and ' . $end_timestamp . ') or (order.state=9 and  order_times.change_work_time<' . $start_timestamp . ' and order.tui_time between ' . $start_timestamp . ' and ' . $end_timestamp . ') ')->field('order.order_id,order.assignor,order.tui_time,user.username,order_times.dispatch_time as created_time,order.state,order.channel_id,order.channel_details,contract.con_time,user.avatar,user.user_id,order_times.change_work_time');
        }

        $_list = $_list->whereIn('user.user_id', $user); //排除禁用的号，测试号，店铺禁用的用户
        if ($type == 3) {
            $_list->where('order.hematopoiesis', '<>', 1);
        }
        $list     = $_list->select();
        $_data    = [];
        $dataList = [];
        $assignor = [];
        foreach ($list as $key => $value) {
            $assignor[]                  = $value['assignor'];
            $_data[$value['assignor']][] = $value;
        }
        $i = 0;
        foreach ($_data as $key => $value) {
            $a                              = 0; //派单量
            $b                              = 0; //无效单
            $c                              = 0; //活动单
            $d                              = 0; //成交单
            $e                              = 0; //有效成交
            $f                              = 0; //有效派单
            $g                              = 0; //成交的活动订单
            $h                              = 0; //退单
            $j                              = 0; //历史成交
            $data[$i]['value_no_order']     = []; //无效单
            $data[$i]['value_act_order']    = []; //活动单
            $data[$i]['value_ok_order']     = []; //成交单
            $data[$i]['value_valid_order']  = []; //有效成交
            $data[$i]['value_send_order']   = []; //有效派单
            $data[$i]['value_act_ok_order'] = []; //成交的活动订单
            $data[$i]['value_back_order']   = []; //退单
            $data[$i]['value_old_order']    = []; //以前派单，在本月签约
            foreach ($value as $k => $v) {

                //创建时间在范围内
                if ($v['created_time'] >= $start_timestamp && $v['created_time'] <= $end_timestamp) {
                    $a++;
                    //无效单
                    if (intval($v['state']) == 10) {
                        $b++;
                        $data[$i]['value_no_order'][] = $v['order_id'];
                    }
                    //活动单
                    if (intval($v['channel_details']) == 96 && intval($v['state']) != 10) {
                        $c++;
                        $data[$i]['value_act_order'][] = $v['order_id'];
                    }
                    //成交单
                    if (intval($v['state']) > 3 && intval($v['state']) < 8) {
                        $d++;
                        $data[$i]['value_ok_order'][] = $v['order_id'];
                    }
                    //有效成交
                    if (intval($v['state']) > 3 && intval($v['state']) < 8 && intval($v['channel_details']) != 96) {
                        $e++;
                        $data[$i]['value_valid_order'][] = $v['order_id'];
                    }
                    //有效派单
                    if (intval($v['channel_details']) != 96 && intval($v['state']) != 10) {
                        $f++;
                        $data[$i]['value_send_order'][] = $v['order_id'];
                    }
                    //成交的活动订单
                    if (intval($v['channel_details']) == 96 && intval($v['state']) > 3 && intval($v['state']) < 8) {
                        $g++;
                        $data[$i]['value_act_ok_order'][] = $v['order_id'];
                    }
                }
                //历史成交(派单时间小于开始时间，成交在时间范围内)
                if ($end_timestamp <= 1704038399) {
                    if ($v['created_time'] < $start_timestamp && $v['con_time'] >= $start_timestamp && $v['con_time'] <= $end_timestamp) {
                        //历史成交订单
                        if (intval($v['state']) > 3 && intval($v['state']) < 8) {
                            $j++;
                            $data[$i]['value_old_order'][] = $v['order_id'];
                            $data[$i]['value_ok_order'][]  = $v['order_id']; //成交单+历史成交
                        }
                    }
                    //退单
                    if ($v['con_time'] > 0 && $v['con_time'] < $start_timestamp && $v['state'] == 9 && $v['tui_time'] >= $start_timestamp && $v['tui_time'] <= $end_timestamp) {
                        $h++;
                        $data[$i]['value_back_order'][] = $v['order_id'];
                        $data[$i]['value_ok_order'][]   = $v['order_id']; //成交单+历史成交
                    }
                } else {
                    if ($v['created_time'] < $start_timestamp && $v['change_work_time'] >= $start_timestamp && $v['change_work_time'] <= $end_timestamp) {
                        //历史成交订单
                        if (intval($v['state']) > 3 && intval($v['state']) < 8) {
                            $j++;
                            $data[$i]['value_old_order'][] = $v['order_id'];
                            $data[$i]['value_ok_order'][]  = $v['order_id']; //成交单+历史成交
                        }
                    }
                    //退单
                    if ($v['change_work_time'] > 0 && $v['change_work_time'] < $start_timestamp && $v['state'] == 9 && $v['tui_time'] >= $start_timestamp && $v['tui_time'] <= $end_timestamp) {
                        $h++;
                        $data[$i]['value_back_order'][] = $v['order_id'];
                        $data[$i]['value_ok_order'][]   = $v['order_id']; //成交单+历史成交
                    }
                }


                $dataList[$i]['username'] = $v['username'];
                $dataList[$i]['avatar']   = $v['avatar'];
                $dataList[$i]['user_id']  = $v['user_id'];
            }
            //有效成交
            $f                            = $f + $g + $j; //+成交的活动订单+以前派单，在本月签约
            $data[$i]['value_send_order'] = array_merge($data[$i]['value_send_order'], $data[$i]['value_act_ok_order'], $data[$i]['value_old_order']);
            //有效成交
            $d = $d + $j - $h; //+以前派单，在本月签约

            //成交率=（成交量—退单）/有效订单（接单量—无效订单—活动订单+成交的活动订单）
            if ($d > 0 && $f > 0) {
                $rate = round($d / $f * 100, 2);
            } elseif ($d > 0 && $f == 0) {
                $rate = 100;
            } else {
                $rate = 0;
            }

            $dataList[$i]['value'] = $rate;
            $i++;
        }


        //        $last_data = array_column($data, 'value');
        //        array_multisort($last_data, SORT_ASC, $data);
        //dd($data);
        return $dataList;
    }

    /*
     * 复购转介绍
     */
    public function Transfe($start_timestamp, $end_timestamp, $user)
    {
        $_list = db('order')->join('user user', 'user.user_id = order.assignor', 'left')->join('order_times', 'order_times.order_id=order.order_id', 'left')->join('contract contract', 'contract.orders_id = order.order_id', 'left')->whereBetween('order_times.change_work_time', [$start_timestamp, $end_timestamp])->whereBetween('order.state', [4, 7])->whereIn('user.user_id', $user); //排除禁用的号，测试号，店铺禁用的用户
        $list  = $_list->field('order.order_id,user.username,user.avatar,
     
        round(((
        sum(if(order.channel_id=27 or order.channel_id=28,1,0)) /count(order.order_id)
        )* 100),2) as value,user.user_id')->group('user.user_id')->select();

        return $list;
    }

    /**
     * 利润排行
     */
    public function profit($start, $end, $user_id)
    {
        $capital = db('capital')->field('sum(to_price) as to_price,ordesr_id')->where(['capital.types' => 1, 'capital.enable' => 1])->group('ordesr_id')->buildSql();

        $envelopes = db('envelopes')->field('sum(envelopes.give_money) as give_money,sum(envelopes.expense) as expense,sum(envelopes.purchasing_discount) as purchasing_discount,sum(envelopes.purchasing_expense) as purchasing_expense,ordesr_id')->where(['envelopes.type' => 1])->group('ordesr_id')->buildSql();
        $fei       = OrderModel::orderMoney($start);
        $order     = \db('order')->field('sum(capitals.to_price + (envelopes.expense-envelopes.give_money-envelopes.purchasing_discount+envelopes.purchasing_expense))- (sum(ifNull(reimbursement,0))+sum(ifNull(settlementReimbursement,0)) +sum(ifNull(material_usage,0)) +sum(if(order.notcost_time=null or order.notcost_time=0,1,0))*' . $fei . ' + sum(ifNull(capitalPersonal,0))) as value,user.username,user.avatar,user.user_id')->join('user', 'user.user_id=order.assignor', 'left')->join([\db('reimbursement')->field('sum(reimbursement.money)as reimbursement,reimbursement.order_id')->where(['reimbursement.classification' => ['<>', 4], 'reimbursement.status' => 1])->group('reimbursement.order_id')->buildSql() => 'reimbursement'], 'reimbursement.order_id=order.order_id  and order.cleared_time between ' . $start . ' and ' . $end . '', 'left')->join([\db('reimbursement')->field('sum(reimbursement.money)as settlementReimbursement,reimbursement.order_id')->where(['reimbursement.classification' => 4, 'reimbursement.status' => 1])->group('reimbursement.order_id')->buildSql() => 'reimbursementSettlement'], 'reimbursementSettlement.order_id=order.order_id and order.settlement_time between ' . $start . ' and ' . $end . '', 'left')->join([Db::connect(config('database.db2'))->table(config('database.db2')['database'] . '.app_user_order_capital')->field('sum(' . config('database.db2')['database'] . '.app_user_order_capital.personal_price) as capitalPersonal,' . config('database.db2')['database'] . '.app_user_order_capital.order_id')->whereNull('app_user_order_capital.deleted_at')->group(config('database.db2')['database'] . '.app_user_order_capital.order_id')->buildSql() => 'capital'], 'capital.order_id=order.order_id', 'left')->join([\db('material_usage')->field('sum(material_usage.total_price)as material_usage,material_usage.order_id')->where(['material_usage.status' => 1])->group('material_usage.order_id')->buildSql() => 'u'], 'u.order_id=order.order_id', 'left')->whereIn('user.user_id', $user_id)->join([$envelopes => 'envelopes'], 'envelopes.ordesr_id=order.order_id', 'left')->join([$capital => 'capitals'], 'capitals.ordesr_id=order.order_id', 'left');


        $orderList = $order->where(function ($quer) use ($start, $end) {
            $quer->whereBetween('order.cleared_time', [$start, $end])->whereOr('order.settlement_time', 'between', [$start, $end]);
        })->group('user.user_id')->select();
        return $orderList;
    }

    public function approvalType()
    {
        r_date([['title' => '全部', 'type' => 0], ['title' => '订单费用报销', 'type' => 1], ['title' => "主材费用报销", 'type' => 2], ['title' => "返工费用报销", 'type' => 3], ['title' => "材料申请(常规)", 'type' => 4], ['title' => "材料申请(采购)", 'type' => 5], ['title' => "减项审批", 'type' => 6], ['title' => "大工地审批", 'type' => 7],['title' => "减量审批", 'type' => 8], ['title' => "退款审批", 'type' => 9], ['title' => "套餐减项审批", 'type' => 10], ['title' => "返工材料审批", 'type' => 11], ['title' => "特权及超限申请", 'type' => 12], ['title' => "超工期申请", 'type' => 13],], 200);
    }

    /*
     * 审批提醒
     */
    public function approvalReminder()
    {
        $data = \request()->get();
        $list = db('approval_info', config('database.zong'))->join('approval', 'approval.approval_id=approval_info.approval_id', 'left')->join('bi_user', 'bi_user.user_id=approval.nxt_id', 'left')->join('order_info', 'order_info.order_id=approval_info.order_id', 'left')->join('user', 'approval_info.user_id=user.user_id', 'left')->join('app_user', 'approval_info.user_id=app_user.id', 'left')->join('order', 'order.order_id=order_info.order_id', 'left');

        if (isset($data['type']) && $data['type'] != 0) {
            $list->where('approval_info.type', $data['type']);
        }
        if (isset($data['status'])) {
            $list->where('approval_info.approval_status', $data['status']);
        }
        if (isset($data['orderId']) && $data['orderId'] != 0) {
            $list->where('approval_info.order_id', $data['orderId']);
        } else {
            $list->where('approval_info.user_id', $this->us['user_id'])->where('approval_info.user_type', 1);
        }

        if (isset($data['examineTime']) && $data['examineTime'] != '') {
            $BeginDate = date('Y-m-01', strtotime($data['examineTime']));
            $starttime = strtotime($BeginDate);
            $enttime   = strtotime(date('Y-m-d', strtotime("$BeginDate +1 month -1 day")));
            $list->whereBetween('approval_info.approval_time', [$starttime, $enttime]);
        }
       // 确定排序字段
        $field = $data['sortord'] == 1 ? 'approval.creation_time' : 'approval_info.approval_time';
        // 确定排序方向
        $direction = $data['sort'] == 1 ? 'ASC' : 'desc';
        // 应用排序
        $list->order("{$field} {$direction}");
//1费用报销，2.主材费用报销，3返工费用报销，4.材料申请(常规)，5.材料申请(采购)，6.减项审批，7.大工地审批，8.减量审批，9.退款审批，10.套餐减项审批，11.返工材料审批，12.特权及超限审批,13.超工期申请审批， 14.师傅点亮星星审批，15.超限+减项，16.延期扣款，17.订单强制结算
        $listArray = $list->page($data['page'], $data['limit'])->field('CASE approval_info.type WHEN 1 THEN "订单费用报销"WHEN 2 THEN "主材费用报销"WHEN 3 THEN "返工费用报销"WHEN 4 THEN "材料申请(常规)"WHEN 5 THEN "材料申请(采购)" WHEN 6 THEN "减项审批" WHEN 7 THEN "大工地审批"  WHEN 8 THEN "减量审批" WHEN 9 THEN "退款审批" WHEN 10 THEN  "套餐减项审批" WHEN 11 THEN  "返工材料领用"  WHEN 12 THEN  "特权及超限申请" WHEN 13 THEN  "超工期申请"  END AS status,ifnull(concat(order_info.county,order.addres),approval_info.title) as  addres,if(ifnull(approval_info.approval_time,0)=0,"",FROM_UNIXTIME(approval_info.approval_time,"%Y-%m-%d %H:%i:%s")) as approvalTime,FROM_UNIXTIME(approval.creation_time,"%Y-%m-%d %H:%i:%s") as creationTime,ifnull(order.contacts,approval_info.reject_user) as contacts,ifnull(order.telephone," ") as telephone,approval_info.reject_desc,if(approval_info.read_time=0,0,1) as readTime ,approval_info.order_id,approval_info.id,CASE approval_info.type WHEN 13 THEN concat("天数：",approval_info.money,"天")
ELSE concat("金额：",approval_info.money,"元") END AS money,approval_info.type,if(approval_info.user_type=1,user.username,app_user.username) as userName,bi_user.username as biUserName')->select();

        $count = db('approval_info', config('database.zong'));

        if (isset($data['type']) && $data['type'] != 0) {
            $count->where('approval_info.type', $data['type']);
        }
        if (isset($data['orderId']) && $data['orderId'] != 0) {
            $count->where('approval_info.order_id', $data['orderId']);
        } else {
            $count->where('approval_info.user_id', $this->us['user_id'])->where('approval_info.user_type', 1);
        }
        if (isset($data['examineTime']) && $data['examineTime'] != 0) {
            $BeginDate = date('Y-m-01', strtotime($data['examineTime']));
            $starttime = strtotime($BeginDate);
            $enttime   = strtotime(date('Y-m-d', strtotime("$BeginDate +1 month -1 day")));
            $count->whereBetween('approval_info.approval_time', [$starttime, $enttime]);
        }
        $count1 = $count->field('approval_status')->select();
        $reject = [];
        $adopt  = [];
        foreach ($count1 as $item) {
            if ($item['approval_status'] == 1) {
                $adopt[] = $item;
            }
            if ($item['approval_status'] == 2) {
                $reject[] = $item;
            }
            
        }
        $listArrayData['data']   = $listArray;
        $listArrayData['adopt']  = count($adopt);
        $listArrayData['reject'] = count($reject);
        r_date($listArrayData, 200);
    }

    /*
     * 审批提醒
     */
    public function clickApprovalReminder()
    {
        $data = \request()->get();
        db('approval_info', config('database.zong'))->where('id', $data['id'])->update(['read_time' => time()]);

        r_date(null, 200);
    }

    /*
    * 日期
  */
    public function workCalendar()
    {
        $data              = \request()->get();
        $work_calendarList = db('work_calendar', config('database.zong'))->whereBetween('data_string', [date('Y-m-d', $data['startWorkCalendar'] / 1000), date('Y-m-d', $data['endWorkCalendar'] / 1000)])->order('data_string ACS')->field('year,month,day,week,is_work,type,data_string')->select();
        r_date($work_calendarList, 200);
    }

    /*
     * 主材代办
     */
    public function mainMaterialAgency()
    {
        $data       = \request()->get();
        $type       = $data['type'];
        $list       = db('order');
        $annotation = '';
        switch ($type) {
            case 1:
                $list->join('agency_task', 'agency_task.order_id=order.order_id', 'left')->join('capital', 'capital.capital_id=agency_task.capital_id', 'left')->where('agency_task.agency_id', 0)->where('agency_task.agency_staff_id', 0)->where('DATEDIFF(NOW(), FROM_UNIXTIME(order.start_time,"%Y-%m-%d"))>=7')->field('order.addres,order.order_id,FROM_UNIXTIME(order.start_time+7*24*3600,"%Y-%m-%d") as createdTime,group_concat(capital.class_b) as title')->where('agency_task.delete_time', 0);
                $annotation = '触发条件:签约日+7天，已到主材下单时间，请及时下单';
                break;
            case 2:
                $list->join('agency_order', 'agency_order.order_id=order.order_id', 'left')->join('agency_task', 'agency_task.agency_order_id=agency_order.id', 'left')->join('capital', 'capital.capital_id=agency_task.capital_id', 'left')->where('agency_order.status', 2)->field('order.addres,order.order_id,if(agency_order.agency_confirm_time=0,FROM_UNIXTIME(agency_order.split_submission_time,"%Y-%m-%d"),FROM_UNIXTIME(agency_order.agency_confirm_time,"%Y-%m-%d")) as createdTime,group_concat(capital.class_b) as title')->where('agency_task.delete_time', 0);
                $annotation = '触发条件:供应商/设计师已出方案，或以提供报价，请确认';
                break;
            case 3:
                $list->join('agency_order', 'agency_order.order_id=order.order_id', 'left')->join('agency_task', 'agency_task.agency_order_id=agency_order.id', 'left')->join('capital', 'capital.capital_id=agency_task.capital_id', 'left')->where('agency_order.status', 3)->field('order.addres,order.order_id,if(agency_order.store_confirm_time=0,"",FROM_UNIXTIME(agency_order.store_confirm_time,"%Y-%m-%d" )) as createdTime,concat(capital.class_b) as title')->where('agency_task.delete_time', 0);
                $annotation = '触发条件:到达用户确认环节，请分享至用户完成方案确认';
                break;
            case 4:
                $list->join('agency_order', 'agency_order.order_id=order.order_id', 'left')->join('agency_task', 'agency_task.agency_order_id=agency_order.id', 'left')->join('capital', 'capital.capital_id=agency_task.capital_id', 'left')->where('agency_order.status', 13)->where('capital.acceptance', 0)->where('capital.types', 1)->where('capital.enable', 1)->where('capital.acceptance', 0)->field('order.addres,order.order_id,if(agency_order.deliverable_time=0,"",FROM_UNIXTIME(agency_order.deliverable_time,"%Y-%m-%d "))  as createdTime,concat(capital.class_b) as title')->where('agency_task.delete_time', 0);
                $annotation = '触发条件:主材已到货或安装完成，需要4天内完成验收';
                break;
            default:
                $auxiliary_delivery_node = \db('auxiliary_delivery_node')->field('auxiliary_delivery_schedul_id,FROM_UNIXTIME(auxiliary_delivery_node.upload_time,"%Y-%m-%d") as createdTime')->where('auxiliary_delivery_node.state', 0)->buildSql();

                $auxiliary_delivery_schedule = \db('auxiliary_delivery_schedule')->field('auxiliary_delivery_schedule.auxiliary_project_list_id,node.*')->join([$auxiliary_delivery_node => 'node'], 'node.auxiliary_delivery_schedul_id=auxiliary_delivery_schedule.id', 'right')->whereNull('auxiliary_delivery_schedule.delete_time')->group('auxiliary_project_list_id')->buildSql();


                $list->field('schedule.*,order.addres,order.order_id,concat(capital.class_b) as title')->join('auxiliary_project_list', 'order.order_id=auxiliary_project_list.order_id', 'left')->join([$auxiliary_delivery_schedule => 'schedule'], 'auxiliary_project_list.id=schedule.auxiliary_project_list_id', 'right')->join('capital', 'capital.capital_id=auxiliary_project_list.capital_id', 'left')->where('order.state', '>', 3)->whereNull('auxiliary_project_list.completion_time')->where('capital.types', 1)->where('capital.enable', 1)->whereNull('auxiliary_project_list.delete_time');
                $annotation = '触发条件:清单巡检节点师傅上传完成，需要你确认质量、工费及完工情况';
                break;
        }
        $data = $list->where('order.assignor', $this->us['user_id'])->where(function ($quer) {
            $quer->whereNull('order.settlement_time')->whereOr('order.settlement_time', 0);
        })->order('createdTime desc')->group('order.order_id')->select();
        foreach ($data as $k => $o) {
            $data[$k]['type']       = $type;
            $data[$k]['annotation'] = $annotation;
        }

        r_date($data, 200);
    }

    /*
     * 待完工的订单
     */
    public function toBeCompleted()
    {
        $time    = strtotime(date('Y-m-d 00:00:00', time()));
        $times   = strtotime(date('Y-m-d 23:59:59', time()));
        $endTime = strtotime(date('Y-m-d 23:59:59', $time + 1 * 24 * 3600));
        $order_abnormal_completion = \db('order_abnormal_completion')->where('delete_time', 0)->whereBetween('created_time', [$time, $times])->column('order_id');
        $list    = \db('startup')
            ->join('order_info', 'startup.orders_id=order_info.order_id', 'left')
            ->join('order_aggregate', 'order_info.order_id=order_aggregate.order_id', 'left')
            ->join('order', 'order.order_id=order_info.order_id', 'left')
            ->whereBetween('startup.up_time', [$time, $endTime])
            ->where('order.assignor', $this->us['user_id'])
            ->whereIn('order.state', [4, 7])
            ->field('concat(order_info.city,order.addres) as addres,order.order_id as orderId,startup.up_time')
            ->whereNull('order.finish_time')->where('order_aggregate.main_price', '>', 0)
            ->whereNotIn('startup.orders_id', $order_abnormal_completion)->select();
        // 将时间戳转换为日期格式
        $formattedData = [];
        foreach ($list as $item) {
            $date                   = date('Y-m-d', $item['up_time']);
            $formattedData[$date][] = $item;
        }
        $tageList = [];
        foreach ($formattedData as $k => $list) {
            $schemeTag['title'] = '明天';
            if ($time <= strtotime($k) && $times >= strtotime($k)) {
                $schemeTag['title'] = '今天';
            }
            $schemeTag['time'] = $k;
            $schemeTag['data'] = $formattedData[$k];
            $tageList[]        = $schemeTag;
        }
        r_date($tageList, 200);
    }

    /*
    * 异常待完工的订单
    */
    public function abnormalCompletion()
    {
        $data            = Authority::param(['jsonData']);
        $time    = strtotime(date('Y-m-d 00:00:00', time()));
        $times   = strtotime(date('Y-m-d 23:59:59', time()));
        $jsonData = json_decode($data['jsonData'], true); //清单
        $listData = [];
        $orderId = 0;
        foreach ($jsonData as $k => $l) {
            $list    = \db('startup')->join('order_info', 'startup.orders_id=order_info.order_id', 'left')->join('order', 'order.order_id=order_info.order_id', 'left')->where('startup.orders_id', $l['orderId'])->field('concat(order_info.city,order.addres) as addres,order.order_id as orderId,startup.sta_time,startup.up_time,order_info.store_name,order_info.assignor,order.store_id,order.order_no')->find();
            $listData[$k]['order_id'] = $l['orderId'];
            $listData[$k]['type'] = $l['checkType'];
            if ($l['checkType'] == 1) {
                $listData[$k]['extension_of_time'] = 0;
                $listData[$k]['reason'] = '';
                $typs = 2;
            } else {
                $listData[$k]['extension_of_time'] = strtotime(date('Y-m-d 23:59:59', strtotime($l['extensionOfTime'])));
                $listData[$k]['reason'] = $l['reason'];
                $typs = 1;
            }
            $listData[$k]['model'] = 2;
            if ($time <= $list['up_time'] && $times >= $list['up_time']) {
                $listData[$k]['model'] = 1;
            }
            abnormalReporting($list['store_name'], $list['assignor'], $list['order_no'], $list['addres'], date('Y-m-d', $list['sta_time']), date('Y-m-d', $list['up_time']), date('Y-m-d', $listData[$k]['extension_of_time']), $listData[$k]['reason'], $typs);
            $listData[$k]['created_time'] = time();
        }
        \db('order_abnormal_completion')->insertAll($listData);

        r_date(null, 200);
    }

    /*
     * 分享
     */
    public function completionSharing()
    {
        $data            = Authority::param(['orderId']);
        $list    = \db('order')->join('order_info', 'order.order_id=order_info.order_id', 'left')->where('order.order_id', $data['orderId'])->field('concat(order_info.city,order.addres) as addres,order.order_id as orderId,order.store_id,order.order_no')->find();
        $post_data = [
            'path' => 'pages/completion/completion',
            'query' => "order_id={$data['orderId']}",
            'env_version' => 'release',
            'expire_type' => 1,
            'expire_interval' => 179
        ];
        $WxService = new WxService();
        $url       = $WxService->getWxUrlLink($post_data);
        $url = explode("?", $url)[1];
        $work_check = Db::connect(config('database.zong'))->table('work_check')->where(['work_check.type' => 1, 'order_id' => $data['orderId'], 'work_check.work_title_id' => 5])->where('delete_time', 0)->find();
        $order_completion = Db::connect(config('database.zong'))->table('order_completion')->where('work_check_id', $work_check['id'])->where('inspection_type', 0)->value('deadline_time');
        completionSharing($list['addres'], date('Y-m-d H:i:s', $order_completion), $list['order_no'], $url);
        $redis         = new RedisPackage();
        $redis::set('completion_sharing_' . $data['orderId'], $data['orderId'], 7200);
        r_date(null, 200);
    }
    /*
     * 用户完工验收异常处理
     */
    public function orderCompletionAbnormal()
    {
        $data            = Authority::param(['id']);
        $list = [];
        $order_completion = Db::connect(config('database.zong'))->table('order_completion')->where('id', $data['id'])->field('issue_images as img,inspection_failure_reason as reason,inspection_failure_content as content,id,order_id')->order('id desc')->find();
        $order_completion['img'] = json_decode($order_completion['img'], true);
        $list['list'] = $order_completion;
        $startup = db('startup')->where('orders_id', $order_completion['order_id'])->value('xiu_id');
        $list['method'] = [
            ['id' => "1", "title" => "施工问题"],
            ['id' => "2", "title" => "主材问题"],
            ['id' => "3", "title" => "其他问题"],
        ];

        $app_user = db('app_user')->whereIn('id', $startup)->where('status', 1)->where('quit_jobs', 0)->field('id,username,origin')->select();
        $order = db('order')

            ->where('order_id', $order_completion['order_id'])
            ->field('order.assignor,order.deliverer')
            ->select();
         $agency_order_sub = db('agency_order_sub',config('database.zong'))
            ->join('agency','agency.agency_id=agency_order_sub.agency_id','left')
            ->where('order_id', $order_completion['order_id'])
            ->field('agency_order_sub.agency_id as  id,agency.agency_name as username')
            ->select();
        $internal = [];
        $external = [];
         foreach ($agency_order_sub as $item) {
           
           array_push($external, [
                    'id' => $item['id'],
                    'username' => $item['username'],
                    'type' => 3
                ]);
        }
        foreach ($app_user as $item) {
            $item['type'] = 2;
            if ($item['origin'] == 1) {
                $internal[] = $item;
            }
            if ($item['origin'] == 2) {
                $external[] = $item;
            }
        }
        foreach ($order as $item) {
            $username1 = db('user')->where('user_id', $item['assignor'])->value('username');

            if ($item['deliverer'] != 0) {
                array_push($internal, [
                    'id' => $item['assignor'],
                    'username' => $username1,
                    'type' => 1
                ]);
                array_push($internal, [
                    'id' => $item['assignor'],
                    'username' => db('user')->where('user_id', $item['deliverer'])->value('username'),
                    'type' => 1
                ]);
            } else {

                array_push($internal, [
                    'id' => $item['assignor'],
                    'username' => $username1,
                    'type' => 1
                ]);
            }
        }
        $list['internal'] = $internal??null;
        $list['external'] = $external??null;
        r_date($list, 200);
    }
      /*
     * 用户完工验收异常处理
     */
    public function orderCompletionAbnormalAdd()
    {
        $data            = Authority::param(['imgs','selectExternalData','des','orderId','selectMethodData','selectInternalData','id']);
        $imgs=json_decode($data['imgs'],true);
        $selectExternalData=json_decode($data['selectExternalData'],true);
        $selectMethodData=json_decode($data['selectMethodData'],true);
        $selectInternalData=json_decode($data['selectInternalData'],true);
        $acceptance_issue=db('acceptance_issue',config('database.zong'))->insertGetId(['responsibility_type'=>$selectMethodData[0]['id'],'user_id'=>$this->us['user_id'],'situation_description'=>$data['des'],'img'=>empty($imgs)?'': $data['imgs'],'create_time'=>time(),'order_completion_id'=>$data['id']]);
        $list=[];
        $order_setting = db('order_setting')->join('order', 'order_setting.order_id=order.order_id', 'left')->field('order_setting.*,order.*')->where('order.order_id', $data['orderId'])->find();
        $order_times = db('order_times')->where('order_id', $data['orderId'])->find();
        foreach($selectExternalData as  $item){
            array_push($list, [
                    'issue_id' => $acceptance_issue,
                    'role' => $item['type'],
                    'user_id' => $item['id'],
                    'create_time' => time(),
                    'username' => $item['username']
                ]); 
        }
        $scoreList=[];
        
        foreach($selectInternalData as  $value){
             array_push($list, [
                    'issue_id' => $acceptance_issue,
                    'role' => $value['type'],
                    'user_id' => $value['id'],
                    'create_time' => time(),
                    'username' => $value['username']
                ]); 
                 $scan_evaluation_score=db('scan_evaluation_score', config('database.zong'))->where(['order_id' => $data['orderId'], 'user_id' =>$value['id'], 'user_type' => 2, 'types' => 10])->find();
                if($value['type']==2 && empty($scan_evaluation_score)){
                    $scoreList[]=['order_id' => $data['orderId'], 'user_id' =>$value['id'], 'user_type' => 2, 'types' => 10, 'user_phone' => db('app_user')->where('id', $value['id'])->value('mobile'), 'relation_id' => $data['id'], 'score' => '-3', 'city_id' => config('cityId'), 'create_time' => time(),'score_time'=>time(),'store_id'=>$order_setting['store_id'],'channel_id'=>$order_setting['channel_id'],'channel_details'=>$order_setting['channel_details'],'pro_id'=>$order_setting['pro_id'],'pro_id1'=>$order_setting['pro_id1'],'pro_id2'=>$order_setting['pro_id2'],'con_time'=>$order_times['signing_time'],'change_work_time'=>$order_times['change_work_time'],'business_id'=>$order_setting['business_id'],'content'=>$order_setting['order_no']];
                }
        }
        db('acceptance_issue_user',config('database.zong'))->insertAll($list);
        if(!empty($scoreList)){
            db('scan_evaluation_score', config('database.zong'))->insertAll($scoreList);
        }
         
        r_date(null, 200);
    }
    /**
     * 邀约到店
     * @return void
     */
    public function inviteTothestore()
    {
        $thirtyDaysAgo = time() - 30 * 86400;
        $clock_in_store = db('envelopes')
            ->alias('e')
            ->join('clock_in_store cis', 'cis.order_id = e.ordesr_id', 'left')
            ->join('order_info', 'e.ordesr_id = order_info.order_id', 'left')
            ->join('order', 'order.order_id = order_info.order_id', 'left')
            ->join('order_setting', 'order.order_id=order_setting.order_id', 'left')
            ->join('order_times', 'order.order_id=order_times.order_id', 'left')
            ->whereNull('cis.id')
            ->whereBetween('order.state', [1, 4])
            ->where('order_times.signing_time', 0)
            ->where('order.assignor', $this->us['user_id'])
            ->where('e.total_price', '>=', 30000)
            ->where('e.created_time', '>=', $thirtyDaysAgo)
            ->group('e.ordesr_id')
            ->field([
                'order.order_id AS order_id',
                'order.contacts AS contacts',
                'order_setting.partnership_config_id',
                // MySQL 8.0+ 用 REGEXP_REPLACE，低版本用 SUBSTRING 拼接
                Db::raw("CONCAT(SUBSTRING(order.telephone, 1, 3), '****', SUBSTRING(order.telephone, 8)) AS telephone"),
                'order_info.pro_id_title AS proTitle',
                'e.total_price AS total_price',
                Db::raw("CONCAT(order_info.county, order.addres, order.street, order.building, order.unit, order.room) AS addres"),
                Db::raw("FLOOR((UNIX_TIMESTAMP(NOW()) - e.created_time) / 86400) AS quote_days")
            ])
            ->order('e.created_time DESC')
            ->select();
        foreach ($clock_in_store as $k => $item) {
            $partnership_config             = Db::connect(config('database.zong'))->table('partnership_config')->where('id', $item['partnership_config_id'])->find();
            $clock_in_store[$k]['tag'] = $partnership_config['tag'];
        }
        r_date(['list' => $clock_in_store, 'count' => count($clock_in_store)], 200);
    }

    /**
     * 待建施工群
     * @return void
     */
    public function constructionGroupToBeBuilt()
    {

        $ConstructionGroup = db('order', config('database.zong'))
            ->join('order_info', 'order.order_id = order_info.order_id', 'left')
            ->join('envelopes', 'envelopes.ordesr_id = order_info.order_id and envelopes.type=1', 'left')
            ->join('order_times', 'order.order_id=order_times.order_id', 'left')
            ->join('order_unique_code', 'order.order_id=order_unique_code.order_id', 'left')
            ->whereBetween('order.state', [4, 7])
            ->where('if(order.order_agency=1,order.cleared_time IS NULL and order.settlement_time  IS NULL,order.cleared_time  IS NULL )')
            ->where('UNIX_TIMESTAMP(FROM_UNIXTIME(order_times.change_work_time,"%Y-%m-%d 00:00:00")) > unix_timestamp(now()) - 4*24*3600')
            ->where('order_unique_code.is_crowd', 0)
            ->where('order.created_time', '>', 1751596804)
            ->where('order.assignor', $this->us['user_id'])
            ->where('order_info.gong', '>', 1)
            ->field([
                'order.order_id AS order_id',
                'order_unique_code.code',
                'order.contacts AS contacts',

                // MySQL 8.0+ 用 REGEXP_REPLACE，低版本用 SUBSTRING 拼接
                Db::raw("CONCAT(SUBSTRING(order.telephone, 1, 3), '****', SUBSTRING(order.telephone, 8)) AS telephone"),
                'order_info.pro_id_title AS proTitle',
                'envelopes.total_price AS total_price',
                Db::raw("CONCAT(order_info.county, order.addres, order.street, order.building, order.unit, order.room) AS addres"),
                Db::raw("FLOOR((UNIX_TIMESTAMP(NOW()) - order_times.envelopes_time) / 86400) AS quote_days")
            ])
            ->order('order_times.signing_time asc')
            ->select();

        r_date($ConstructionGroup, 200);
    }
    /**
     * 施工交底
     */
    public function startupNewHandover(){
          $userId=$this->us['user_id'];
          $new_handover = db('order')->field([
            'order.order_id',
            'order.cleared_time',
            'order.settlement_time',
            'ANY_VALUE(order.addres) as address', // 非聚合字段处理‌:ml-citation{ref="6" data="citationList"}
            // 'startup.sta_time',
            'FROM_UNIXTIME(order_times.signing_time,"%Y-%m-%d %H:%i:%s")as signing_time',
            // 'MAX(order_times.change_work_time) as change_work_time',
            'SUM(IF(startup_new_handover.type=1,1,0)) as type1_total',
            'SUM(IF(startup_new_handover.type=2,1,0)) as type2_total'
        ])
            ->join('order_aggregate', 'order.order_id=order_aggregate.order_id', 'left')
            ->join('startup_new_handover', 'order.order_id=startup_new_handover.order_id AND startup_new_handover.delete_time=0', 'LEFT')
            ->join('order_times', 'order_times.order_id=order.order_id', 'LEFT')
            
            ->where(function ($query) use ($userId) {
                $query->where('order.assignor', $userId);
                    // ->whereOr('order.deliverer', $userId);
            })->where('CASE
            WHEN order_aggregate.main_price=0 and order_aggregate.agent_price !=0  THEN order.settlement_time is  null or order.settlement_time =0
             WHEN order_aggregate.main_price !=0 and order_aggregate.agent_price =0  THEN  order.cleared_time is  null or order.cleared_time =0
             WHEN order_aggregate.main_price !=0 and order_aggregate.agent_price !=0 THEN  order.cleared_time is  null or order.cleared_time =0 or order.settlement_time is null or order.settlement_time =0 END
             ')
            ->where('order_times.change_work_time', '>', 1743134400)
            ->whereNull('order.cleared_time')
           ->group('startup_new_handover.order_id')
            ->having('type1_total = 0 OR type2_total = 0 OR startup_new_handover.order_id IS NULL')
            ->select();
        if (!empty($new_handover)) {
            foreach ($new_handover as $l => $item) {
  
                $new_handover[$l]['content'] = '签约后为交底工地';
                $new_handover[$l]['type'] = 2;
                unset($new_handover[$l]['type2_total'], $new_handover[$l]['type1_total']);
            }
        }
         r_date($new_handover, 200);   
    }
    /**
     * 管理员盘单
     * @return void
     */
    public function administratorDiskOrder()
    {
        $ConstructionGroup = db('order_inventory_record', config('database.zong'))
            ->join('order', 'order.order_id = order_inventory_record.order_id', 'left')
            ->join('order_info', 'order.order_id = order_info.order_id', 'left')
            ->field([
                'order.order_id AS order_id',
                'order_info.pro_id_title as title',
                'order.contacts AS contacts',
                'if(order_inventory_record.read_time !=0,1,0) as readTime',
                // MySQL 8.0+ 用 REGEXP_REPLACE，低版本用 SUBSTRING 拼接
                Db::raw("CONCAT(SUBSTRING(order.telephone, 1, 3), '****', SUBSTRING(order.telephone, 8)) AS telephone"),

                Db::raw("CONCAT(order_info.county, order.addres, order.street, order.building, order.unit, order.room) AS addres"),
                'order_inventory_record.content',

                Db::raw("CONCAT(order_inventory_record.bi_user_name,'[于', FROM_UNIXTIME(order_inventory_record.created_time,'%Y-%m-%d %H:%i:%s'),'添加]') AS manager"),
                'if(order_inventory_record.next_time !=0,FROM_UNIXTIME(order_inventory_record.next_time,"%Y-%m-%d %H:%i:%s"),"") as nextTime'
            ])
            ->order('order_inventory_record.created_time asc')
            ->where('order.assignor',$this->us['user_id'])
            ->select();
        r_date($ConstructionGroup, 200);
    }
}
